package com.pocket.insight.base.sql.dataset.resource;

import com.alibaba.druid.sql.SQLUtils;
import com.alibaba.druid.sql.ast.SQLStatement;
import com.alibaba.druid.sql.ast.statement.SQLSelectStatement;
import com.google.common.base.Strings;
import com.pocket.insight.base.cache.CacheStorage;
import com.pocket.insight.base.constant.SystemConfigConst;
import com.pocket.insight.base.context.AppParamContext;
import com.pocket.insight.base.exception.BizException;
import com.pocket.insight.base.mql.Relation;
import com.pocket.insight.base.mql.TypedVal;
import com.pocket.insight.base.mql.relation.StatementRelation;
import com.pocket.insight.base.sql.Dialect;
import com.pocket.insight.base.sql.SqlEval;
import com.pocket.insight.base.sql.Vendor;
import com.pocket.insight.base.sql.constant.PatternConst;
import com.pocket.insight.base.sql.context.ChartSampleContext;
import com.pocket.insight.base.sql.context.SpecifiedVendorContext;
import com.pocket.insight.base.mql.relation.PrimeRelation;
import com.pocket.insight.base.sql.dataset.DatasetUtil;
import com.pocket.insight.base.sql.dataset.RuntimeData;
import com.pocket.insight.base.sql.factory.VendorFactory;
import com.pocket.insight.base.sql.rewriter.ParamAttrRewriter;
import com.pocket.insight.controller.dto.DatasetResultDto;
import com.pocket.insight.entity.Connection;
import com.pocket.insight.entity.Dataset;
import com.pocket.insight.entity.ME;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.commons.lang3.BooleanUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.commons.lang3.tuple.ImmutablePair;
import org.apache.commons.lang3.tuple.Pair;
import org.jparsec.error.ParserException;

import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * 描述：
 *
 * @author shuize
 * @create 2024/12/5
 */
@Slf4j
public class NativeResourceCreator extends BaseResourceCreator {

    private static final Set<String> ILLEGAL_CUSTOM_SQL_PREFIX = Set.of("insert", "update", "delete", "alter", "set", "excute", "declare", "use", "show");
    private static final Pattern     SIMPLE_SQL_PATTERN        = Pattern.compile("^\\s*select\\s+\\*\\s+from\\s+\\S+\\s*$", 2);
    private static final Pattern     SIMPLE_SQL_PREFIX_PATTERN = Pattern.compile("^\\s*select\\s+\\*\\s+from\\s+", 2);

    public NativeResourceCreator(Dataset dataset, RuntimeData runtimeData) {
        super(runtimeData);
        this.datasetResource.setDataset(dataset);
    }

    @Override
    public Vendor createVendor() {
        if (SpecifiedVendorContext.getVendorName() != null) {
            Vendor vVendor = VendorFactory.createVendor(SpecifiedVendorContext.getVendorName());
            vVendor.setOptions(new Connection.ConnectionOptions());
            return vVendor;
        }
        Dataset dataset = this.datasetResource.getDataset();
        Connection connection = connectionService.getById(dataset.getOptions().getConnectionId());
        if (connection == null) {
            throw new BizException("ERROR_NOT_FOUND: " + dataset.getOptions().getConnectionTitle());
        }
        Vendor vendor = VendorFactory.createQueryVendor(connection.getOptions());
        return vendor;
    }

    @Override
    protected Relation createDatasetRelationWithoutFilters() {
        Dataset.Options options = this.datasetResource.getDataset().getOptions();
        Vendor vendor = this.datasetResource.getVendor();

        List<String> path = vendor.rewriterPath(options.getPath());
        Dialect dialect = vendor.getDialect();
        TypedVal tableQuery = null;
        List<Relation> withsInSql = new ArrayList();
        boolean[] withRecursive = new boolean[]{false};
        String customSql = null;
        String table;
        if (StringUtils.isNotBlank(options.getCustomSql())) {
            customSql = (ParamAttrRewriter.rewriteClassWithSymbolCheck(options, dialect, AppParamContext.getAppParamValueMap(this.datasetResource.getDataset().getAppId()))).getCustomSql();
            table = CollectionUtils.isNotEmpty(path) ? vendor.getDialect().quotePath(path) : null;
            customSql = this.rewriteMacro(customSql, path, table);
            boolean[] updateToTable = new boolean[]{false};
            customSql = this.reformCustomSql(customSql, path, table, withsInSql, withRecursive, updateToTable);
            if (updateToTable[0]) {
                tableQuery = new TypedVal(customSql);
                customSql = null;
            }
        }

        if (tableQuery == null) {
            if (StringUtils.isNotEmpty(customSql)) {
                tableQuery = dialect.reformCustomQuery(customSql);
            } else if (CollectionUtils.isNotEmpty(path)) {
                table = options.getTable();
                List<String> tablePath = new ArrayList(path);
                if (options.isFile()) {
                    tablePath = new ArrayList(options.getPath());
                }

                tablePath.add(table);
                tableQuery = dialect.createTableVal(tablePath);
            } else {
                table = vendor.reformTableName(options.getTable());
                tableQuery = dialect.createTableVal(new ArrayList(Arrays.asList(table)));
            }
        }

        tableQuery.setBaseTableAlias(SqlEval.idToAlias(this.datasetResource.getDataset().getId()));
        Relation relation = PrimeRelation.create(dialect, ME.create(ME.Kind.dataset, tableQuery));
        if (BooleanUtils.isTrue(ChartSampleContext.getSample())) {
            relation.setAlias(this.getDatasetResource().getTableAlias());
            relation = wrapWithLimit(relation);
        }

        if (!withsInSql.isEmpty()) {
            relation.getWith().addAll(withsInSql);
        }

        if (withRecursive[0]) {
            relation.setWithRecursive(true);
        }

        return relation;
    }


    private String rewriteMacro(String customSql, List<String> path, String tablePrefix) {
        Pattern pattern = Pattern.compile("\\{\\{!!.+?\\}\\}", 32);
        Matcher matcher = pattern.matcher(customSql);
        Set<String> usedMacro = new HashSet();
        while(matcher.find()) {
            String parameter = matcher.group();
            usedMacro.add(parameter);
        }
        if (usedMacro.isEmpty()) {
            return customSql;
        } else {
            String p;
            DatasetResultDto resultDto;
            for(Iterator var10 = usedMacro.iterator(); var10.hasNext(); customSql = customSql.replace(p, ((List)resultDto.fetchData().get(0)).get(0).toString())) {
                p = (String)var10.next();
                resultDto = this.getDatasetResource().getVendor().fetchSqlDataWithThreadCache(this.reformCustomSql(p.substring(4, p.length() - 2), path, tablePrefix, (List)null, (boolean[])null, (boolean[])null));
                if (resultDto == null || CollectionUtils.isEmpty(resultDto.fetchData()) || resultDto.fetchData().size() > 1 || CollectionUtils.isEmpty((Collection)resultDto.fetchData().get(0)) || ((List)resultDto.fetchData().get(0)).size() > 1 || ((List)resultDto.fetchData().get(0)).get(0) == null) {
                    throw new BizException("ERROR_MACRO_MUST_RETURN_ONE_DATA");
                }
            }
            return customSql;
        }
    }

    private String reformCustomSql(String customSql, List<String> path, String tablePrefix, List<Relation> withsInSql, boolean[] withRecursive, boolean[] updateToTable) {
        if (this.datasetResource.getVendor().getDialect().isNoSqlDialect()) {
            return customSql;
        } else {
            try {
                if (CollectionUtils.isNotEmpty(path)) {
                    tablePrefix = this.datasetResource.getVendor().getDialect().quotePath(path);
                }
                customSql = this.removeFootlineSingleLineComments(customSql);
                this.simpleCheckCustomSql(customSql);
                try {
                    Pair<String, String> tablePrefixAndSql = this.datasetResource.getVendor().reformSql(tablePrefix, customSql);
                    customSql = tablePrefixAndSql.getValue();
                    tablePrefix = tablePrefixAndSql.getKey();
                    if (updateToTable != null) {
                        String table = this.tryToSimplifySql(customSql, tablePrefix, path);
                        if (table != null) {
                            updateToTable[0] = true;
                            return table;
                        }
                    }
                } catch (com.alibaba.druid.sql.parser.ParserException parserException) {
                    log.error("Druid sql parse error 1: " + customSql, parserException);
                }

                customSql = this.reformCustomSqlWithConnectionAuth(customSql, tablePrefix);
            } catch (BizException bizException) {
                throw bizException;
            } catch (Exception exception) {
                log.error(exception.getMessage(), exception);
                log.warn("reformSql fail! use origin sql: {}", customSql);
            }

            return this.abstractWithFromSql(customSql, withsInSql, withRecursive);
        }
    }

    public static Relation wrapWithLimit(Relation primeRelation) {
        StatementRelation relation = Relation.statement(primeRelation.getDialect()).limit(SystemConfigConst.SAMPLE_COUNT);
        relation.from = primeRelation;
        return relation;
    }

    private String removeFootlineSingleLineComments(String customSql) {
        List<String> lines = new ArrayList(Arrays.asList(customSql.split("\n")));
        ListIterator<String> it = lines.listIterator();

        while(it.hasNext()) {
            String line = it.next();
            if (line.startsWith("--")) {
                it.remove();
            } else if (PatternConst.FOOT_LINE_COMMENT_PATTERN.matcher(line).matches()) {
                it.set(line.substring(0, this.getIndexOfHyphen(line, 0)));
            }
        }
        return StringUtils.join(lines, "\n");
    }

    private int getIndexOfHyphen(String sql, int startIndex) {
        int i1 = sql.indexOf("--", startIndex);
        int i2 = sql.indexOf("-->", startIndex);
        return i2 > 0 && i1 == i2 ? this.getIndexOfHyphen(sql, i2 + 3) : i1;
    }


    private void simpleCheckCustomSql(String customSql) {
        customSql = customSql.trim();
        Iterator var2 = ILLEGAL_CUSTOM_SQL_PREFIX.iterator();
        String prefix;
        do {
            if (!var2.hasNext()) {
                return;
            }
            prefix = (String)var2.next();
        } while(!StringUtils.startsWithIgnoreCase(customSql, prefix));
        throw new BizException("ERROR_CUSTOM_SQL_NON_QUERY_STATEMENT");
    }


    private String tryToSimplifySql(String customSql, String tablePrefix, List<String> path) {
        if (SIMPLE_SQL_PATTERN.matcher(customSql).find()) {
            Matcher m = SIMPLE_SQL_PREFIX_PATTERN.matcher(customSql);

            String table;
            do {
                if (!m.find()) {
                    return null;
                }

                table = customSql.replace(m.group(0), "").trim();
                if (table.endsWith(";")) {
                    table = table.substring(0, table.length() - 1);
                }
            } while((!Strings.isNullOrEmpty(tablePrefix) || table.contains(".")) && (Strings.isNullOrEmpty(tablePrefix) || !table.startsWith(tablePrefix)));

            Dataset tmpDataset = new Dataset();
            tmpDataset.setOptions(new Dataset.Options());
            tmpDataset.getOptions().setPath(path);
            tmpDataset.getOptions().setTable(this.datasetResource.getVendor().getDialect().unquoteSingleEntity(Strings.isNullOrEmpty(tablePrefix) ? table : table.replaceFirst(tablePrefix + ".", "")));
            tmpDataset.getOptions().setType(Dataset.Options.DatasetTypeEnum.connection);
            tmpDataset.getOptions().setConnectionId(this.datasetResource.getDataset().getOptions().getConnectionId());
            tmpDataset.setAppId(this.datasetResource.getDataset().getAppId());
            return table;
        } else {
            return null;
        }
    }


    private String reformCustomSqlWithConnectionAuth(String customSql, String tablePrefix) {
        // TODO
        return customSql;
    }


    private String abstractWithFromSql(String sql, List<Relation> withs, boolean[] withRecursive) {
        if (withs != null && !this.getDatasetResource().getVendor().getDialect().supportWithInSubquery() && StringUtils.startsWithIgnoreCase(sql.trim(), "with ")) {
            List<SQLStatement> stmtList = SQLUtils.parseStatements(sql, this.datasetResource.getVendor().getDbType());
            if (stmtList.size() == 1 && stmtList.get(0) instanceof SQLSelectStatement) {
                try {
                    SQLSelectStatement sqlSelectStatement = (SQLSelectStatement)stmtList.get(0);
                    if (sqlSelectStatement.getSelect().getWithSubQuery() == null) {
                        return sql;
                    } else {
                        sqlSelectStatement.getSelect().getWithSubQuery().getEntries().forEach((entry) -> {
                            String withStr = this.datasetResource.getVendor().reformatSpecialFunctionInSql(SQLUtils.toSQLString(entry.getSubQuery(), this.datasetResource.getVendor().getDbType()));
                            String entryAlias = this.datasetResource.getVendor().getDialect().unquoteSingleEntity(entry.getAlias());
                            withs.add(PrimeRelation.create(this.datasetResource.getVendor().getDialect(), ME.create(ME.Kind.dataset, withStr)).setAlias(entryAlias));
                            Pair<String, String> datasetKeyWithStrPair = this.runtimeData.getShareWithMap().get(entryAlias);
                            if (datasetKeyWithStrPair != null) {
                                if (!withStr.equals(datasetKeyWithStrPair.getRight())) {
                                    throw new BizException("ERROR_DIFFERENT_WITH_IN_CUSTOM_SQL: " + this.datasetResource.getDataset().getTitle() + datasetKeyWithStrPair.getLeft() + entryAlias);
                                }
                            } else {
                                this.runtimeData.getShareWithMap().put(entryAlias, new ImmutablePair(this.datasetResource.getDataset().getTitle(), withStr));
                            }

                        });
                        if (BooleanUtils.isTrue(sqlSelectStatement.getSelect().getWithSubQuery().getRecursive())) {
                            withRecursive[0] = true;
                        }

                        return this.datasetResource.getVendor().reformatSpecialFunctionInSql(SQLUtils.toSQLString(sqlSelectStatement.getSelect().getQuery(), this.datasetResource.getVendor().getDbType()));
                    }
                } catch (BizException bizException) {
                    throw bizException;
                } catch (Exception exception) {
                    return sql;
                }
            } else {
                return sql;
            }
        } else {
            return sql;
        }
    }

}
